# ========增加代码--开始========
import logging
import time
import uuid
import os
from tornado.web import RequestHandler
import json
import sqlite3
import math

from threading import Lock

'''
classlabel 一级目录
mattype 二级目录
'''

lock = Lock()

def get_myargument(handler, **kwargs):
    keys = ()
    for key in kwargs:
        if key == 'file':
            keys += (handler.request.files.get(key, kwargs[key]),)
        else:
            keys += (handler.get_argument(key, kwargs[key]),)
    return keys


def outputRes(handler, Res, **kwargs):
    for key in kwargs:
        Res[key] = kwargs[key]
    handler.write(json.dumps(Res, ensure_ascii=False).encode('utf8'))



class BaseHandler(RequestHandler):
    def set_default_headers(self):
        self.set_header("Access-Control-Allow-Origin", "*")
        self.set_header("Access-Control-Allow-Headers",
                        "x-requested-with,access_token")  # 这里要填写上请求带过来的Access-Control-Allow-Headers参数，如access_token就是我请求带过来的参数
        self.set_header("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE")  # 请求允许的方法
        self.set_header("Access-Control-Max-Age", "3600")  # 用来指定本次预检请求的有效期，单位为秒，，在此期间不用发出另一条预检请求。

    # 定义一个响应OPTIONS 请求，不用作任务处理
    def options(self):
        pass

class SQLConstructor:
    def __init__(self):
        pass

    def select(self, select_clause ,from_tablename, where_clause=[], where_clause2='' ,order_clause=None, limit_clause=''):
        sql = 'select ' + select_clause + ' from ' + from_tablename
        if where_clause != []:
            sql += ' where'
            for key in where_clause:
                sql += ' %s=? and' % (key, )
        if where_clause == [] and where_clause2 != '':
            sql += ' where %s ' % (where_clause2,)
        else:
            sql += ' %s ' % (where_clause2,)
        if order_clause:
            if order_clause['type'] != 0:
                sql += ' order by %s %s' % (order_clause['orderby'], 'desc' if order_clause['type'] == 2 else '')
        if limit_clause:
            sql += ' %s' % (limit_clause,)
        return sql

class CalcPaging:
    def __init__(self, pageNo, pageSize, totalNum):
        self.pageNo = pageNo
        self.pageSize = pageSize
        self.totalNum = totalNum

    def getPageNum(self):
        return int(math.ceil((self.totalNum) * 1.0 / self.pageSize))

class PagingDB:
    def __init__(self):
        self.dbpath = ''
        self.dbkeys2retkeys = {}  # 数据库中的字段名->返回给前端的字段名映射
        self.tablename = ''
        self.conn = None
        self.cur = None
        self.isconnect = False
        self.ret = None

    def check(self):
        self.dbpath != '', 'server error'
        self.dbkeys2retkeys != {}, 'server error'
        self.tabelname != '', 'server error'

    def additems(self, args):
        self.cur.executemany(self.addsql, args)

    def changeitemsbyid(self, args):
        sqlset = ''
        for item in self.dbkeys2retkeys:
            if item == 'id': continue
            if item == 'updateDate':
                sqlset += 'updateDate=datetime("now", "localtime"),'
                continue
            sqlset += item + '=?,'
        sqlset = sqlset[:-1]
        sql = 'update %s set %s where id=?' % (self.tablename, sqlset)
        print((sql, args))
        self.cur.executemany(sql, args)

    def reset_order(self, args):
        sql = 'update %s set sOrder=? where sName=?' % (self.tablename,)
        print((sql, args))
        for s in args:
            self.cur.execute(sql, s)

    def getSearchTuple(self, searchKey):      #生成搜索的sql条件
        dbkeys = list(self.dbkeys2retkeys.keys()).copy()
        dbkeys.remove('id')
        ret = ''
        for i in range(len(dbkeys)):
            if i != len(dbkeys)-1:ret += ' instr(%s, "%s") > 0 or' % (dbkeys[i], searchKey)
            else: ret += ' instr(%s, "%s") > 0' % (dbkeys[i], searchKey)
        return ret

    def getcountfromtable(self, queryValue, qdsoftid):  #获取总共有几条数据
        SearcSqlTuple = ''
        if (queryValue == None or queryValue == ''):
            sql = 'select Count(*) from %s where qdsoftid=?' % self.tablename
            datalist = self.execute(sql, (qdsoftid,),ret=True)
        else:
            if queryValue != None and queryValue != '':
                SearcSqlTuple = '(%s)' % self.getSearchTuple(queryValue)
            sql = 'select Count(*) from %s where %s and qdsoftid=?' % (self.tablename, SearcSqlTuple)
            datalist = self.execute(sql, (qdsoftid, ), ret=True)
        return datalist[0][0]

    def getcountfromtable_noqdsoft(self, queryValue):
        SearcSqlTuple = ''
        if queryValue != None and queryValue != '':
            SearcSqlTuple = '(%s)' % self.getSearchTuple(queryValue)
        sqlobj = SQLConstructor()
        sql = sqlobj.select('Count(*)', self.tablename, where_clause2=SearcSqlTuple)
        datalist = self.execute(sql, None, ret=True)
        return datalist[0][0]


    def finditembyqdsoft_paging(self, pageNo, itemNum, queryValue, qdsoftid):
        '''
        1.获取分组
        2.获取搜索关键字
        3.在1，2 获得的数据基础上分页
        :param qdsoftid:  软件标识
        :param pageNo:   页码
        :param itemNum: 每页多少条
        :param myClass: 分组
        :return:
        '''
        searchTuple = ''
        offsetNum = (pageNo - 1) * itemNum
        if queryValue != None and queryValue != '':
            searchTuple = '(%s)' % self.getSearchTuple(queryValue)
        if searchTuple:
            sql = 'select * from %s where %s and qdsoftid=? limit %d offset %d' % (self.tablename, searchTuple, itemNum, offsetNum)
        else:
            sql = 'select * from %s where qdsoftid=? limit %d offset %d' % (self.tablename, itemNum, offsetNum)
        datalist = self.execute(sql, (qdsoftid, ),  ret=True)
        print(self.retkeys2dbkeys)
        reslist = []
        for item in datalist:
            resitem = {}
            for i in range(len(list(self.retkeys2dbkeys.keys()))):
                resitem[list(self.retkeys2dbkeys.keys())[i]] = item[i]
            reslist.append(resitem)
        return reslist

    def finditem_paging(self, pageNo, itemNum, queryValue, orderby):
        '''
        1.获取分组
        2.获取搜索关键字
        3.在1，2 获得的数据基础上分页
        :param qdsoftid:  软件标识
        :param pageNo:   页码
        :param itemNum: 每页多少条
        :param myClass: 分组
        :return:
        '''
        searchTuple = ''
        offsetNum = (pageNo - 1) * itemNum
        if queryValue != None and queryValue != '':
            searchTuple = '(%s)' % self.getSearchTuple(queryValue)
        sqlobj = SQLConstructor()
        sql = sqlobj.select('*', self.tablename, where_clause2=searchTuple, order_clause=orderby, limit_clause='limit %d offset %d' % (itemNum, offsetNum))
        print(sql)
        datalist = self.execute(sql, None,  ret=True)
        print(self.retkeys2dbkeys)
        reslist = []
        for item in datalist:
            resitem = {}
            for i in range(len(list(self.retkeys2dbkeys.keys()))):
                resitem[list(self.retkeys2dbkeys.keys())[i]] = item[i]
            reslist.append(resitem)
        return reslist

    def deleteitemsbyid(self, args):
        sql = 'delete from %s where id=?' % self.tablename
        self.cur.executemany(sql, args)

    def findallitem_noqdsoft(self, orderby):
        sqlobj = SQLConstructor()
        sql = sqlobj.select('*', self.tablename, order_clause=orderby)
        datalist = self.execute(sql, None, ret=True)
        reslist = []
        for item in datalist:
            resitem = {}
            for i in range(len(list(self.retkeys2dbkeys.keys()))):
                resitem[list(self.retkeys2dbkeys.keys())[i]] = item[i]
            reslist.append(resitem)
        return reslist

    def findallitem(self, qdsoftid):
        sql = 'select * from %s where qdsoftid=?' % (self.tablename)
        datalist = self.execute(sql, (qdsoftid, ), ret = True)
        reslist = []
        for item in datalist:
            resitem = {}
            for i in range(len(list(self.retkeys2dbkeys.keys()))):
                resitem[list(self.retkeys2dbkeys.keys())[i]] = item[i]
            reslist.append(resitem)
        return reslist

    def execute(self, sql, sqltuple, ret=False):
        if sqltuple is not None:
            self.cur.execute(sql, sqltuple)
        else:
            self.cur.execute(sql)
        if ret:
            return self.cur.fetchall()
        else:
            return None

    def connect(self):
        self.conn = sqlite3.connect(self.dbpath)
        self.cur = self.conn.cursor()
        self.isconnect = True

    def commitandclose(self):
        self.conn.commit()
        self.conn.close()
        self.isconnect = False


class SQLStr:
    StateList = ['DataBaseType', 'SQLBegin', 'SQLSTR', 'SQLEND']
    SYNTAX = ['[', 'SQLBegin' ,'Name', ']', 'Content', '[', 'End', ']']
    def __init__(self, filepath):
        self.__StrObj = {}
        self.StrKey = []
        self.__State = 'DataBaseType'  #特定状态下期待不同的输入

        self.Analysis(filepath)
        for key in self.__StrObj:
            self.StrKey.append(key)

    def __getitem__(self, item):
        return self.__StrObj.get(item, None)

    def Analysis(self, filepath):
        if os.path.exists(filepath):
            r = open(filepath, 'r')
            for line in r:
                line1 = line.replace('\n', ' ')
                if line1.replace(' ', '') == '': continue
                if line1[0] == '#' and line1[1:] != 'eof':
                    self.__State = 'SQLBegin'
                elif line1.find('[SQLBegin') != -1:
                    if self.__State != 'SQLBegin':
                        raise SyntaxError('%s is expected but %s' % (self.__State, line1))
                    begin_args = line1.replace(' ', '')[1:-1]
                    begin_args = begin_args.split(',')
                    Name = begin_args[1]
                    sqlargs = begin_args[2:]
                    if self.__StrObj.get(Name, None) is None:
                        self.__StrObj[Name] = {
                            'SQL': '',
                            'arglist': sqlargs
                        }
                    else:
                        self.__StrObj[Name]['arglist'] = sqlargs
                    self.__State = 'SQLSTR'
                elif line1.find('[') == -1 and line1.find('#') == -1 and self.__State == 'SQLSTR':
                    self.__StrObj[Name]['SQL'] += line1
                elif line1.find('[End]') != -1:
                    Name = None
                    sqlargs = None
                    self.__State = 'SQLBegin'
                elif line1 == '#eof':
                    break
            r.close()
            

class Name_Id_Tree:
    def __init__(self):
        self.data = {}
        '''
          path2 :name1\\name1-1\\name1-3
          name1:{  
                id: xxxx,
                name1-1:{
                  id:xxxx,
                  name1-3:{}
                },
                name1-2:{
                  id :xxx
                }
          }
        用于带有树结构特征的数据表的迁移
        '''

    def path2_to_idpath(self, path2):
        path2list = path2.split('\\')
        idpathlist = []
        p = self.data
        for name in path2list:
            if name == '模板目录':
                p = p['@XML']
            else:
                p = p[name]
            idpathlist.append(str(p['id']))
        return '/'.join(idpathlist)

    def add2name_to_id(self, path2, id):
        path2list = path2.split('\\')
        p = self.data
        for path in path2list:
            if path == '模板目录':
                if p.get('@XML', None) == None:
                    p['@XML'] = {}
                    p['@XML']['id'] = id
                else:
                    p = p['@XML']
            else:
                if p.get(path, None) == None:
                    p[path] = {}
                    p[path]['id'] = id
                else:
                    p = p[path]
    def exists(self, path2):
        path2list = path2.split('\\')
        p = self.data
        for path in path2list:
            p = p.get(path, None)
        if p == None: return False
        else: return True

    def get_sOrder(self, path2):
        path2list = path2.split('\\')
        p = self.data
        for path in path2list:
            if path == '模板目录':
                p = p['@XML']
            else:
                p = p[path]
        return len(p) - 1

    def get_name_to_id(self, path2):
        path2list =path2.split('\\')
        p = self.data
        for path in path2list:
            p = p[path]
        return p['id']

#sqlconfig = SQLStr('C:\\Users\\qhh\\Desktop\\1.txt')

class MyDataBase:  #默认sqlite3
    def __init__(self, databasename, logger):
        self.databasename = databasename
        self.__isconnected = False
        self.conn = None
        self.cursor = None
        self.logger = logger
        self.islocked = False

    def __del__(self):
        if self.__isconnected == True:
            self.disconnect()


    def connect(self):
        if not self.__isconnected:
            if not self.islocked :
                lock.acquire()
                self.islocked = True
            self.conn = sqlite3.connect(self.databasename)
            self.cursor = self.conn.cursor()
            self.__isconnected = True
            if self.logger != None :self.logger.info('连接数据库')

    def disconnect(self, iscommit=True):
        if self.__isconnected:
            if self.islocked:
                lock.release()
                self.islocked = False
            if iscommit: self.conn.commit()
            self.conn.close()
            self.__isconnected = False
            if self.logger :self.logger.info('关闭数据库')

    def execute(self, sql, args_tuple=None, islog=True):
        if islog:
            args = ()
            if args_tuple != None:
                for arg in args_tuple:
                    if type(arg) is str:
                        if len(arg) > 100:
                            args += ('...data too long', )
                        else:
                            args += (arg, )
                    else:
                        args += (arg,)
            else:
                args = args_tuple
            self.logger.info((sql , args))
        if args_tuple == None:
            self.cursor.execute(sql)
        else:
            self.cursor.execute(sql, args_tuple)

    def executemany(self, sql, array):
        self.cursor.executemany(sql, array)


    def lastrowid(self):
        return self.cursor.lastrowid

    def fetchall(self):
        return self.cursor.fetchall()

class TreeDataBase(MyDataBase):
    def AddItem(self, tablename, keylist, valuelist):
        sql = 'INSERT INTO %s (%s) valuses (%s)'


# import MySQLdb
# class MyDataBase_MySQL(MyDataBase):
#     def __init__(self, databasename, logger):
#         super(MyDataBase_MySQL, self).__init__(databasename, logger)
#
#     def connect(self):
#         if not self.__isconnected:
#             if not self.islocked:
#                 lock.acquire()
#                 self.islocked = True
#             self.conn = MySQLdb.connect(host='localhost', user='qhh', port=3306, password='7892332',
#                                         database=os.path.basename(self.databasename).replace('.db', ''), charset="utf8")
#             self.cursor = self.conn.cursor()
#             self.__isconnected = True
#             if self.logger: self.logger.info('连接数据库')
#
#     def execute(self, sql, args_tuple=None, islog=True):
#         sql = sql.replace('?', '%s')
#         super(MyDataBase_MySQL, self).execute(sql, args_tuple, islog)